/*
 * 访问数据库
 * 
 */

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import context.Context;
import exception.ViewException;

public class NewPayRecordDAO {

	public static ResultSet getNewPayRecord(String quaryID) throws ViewException{
		Context c = Context.newInstance();

		Statement st = null;

		ResultSet rs = null;

		// ResultSetMetaData rsmd = null;

		String tableName = "newpayrecord";

		// String quaryID = "'5088010100000011771'";


		try {

			Class.forName("com.mysql.jdbc.Driver");


			Connection con = DriverManager.getConnection(c.getConnection(),
					c.getUsrName(), c.getPsw());

			st = con.createStatement();

			String sqlx = "select tradeDate,tradeTime, amount from "
					+ tableName + " where cardID " + " = '" + quaryID + "'";
			// String sqltotal =
			// "select tradeDate, sum(amount) from newpayrecord where tradeType = '01C00121' or tradeType = 'Z3004' group by tradeDate";

			// / String sqlall =
			// "select tradeDate,tradeTime, sum(amount) from newpayrecord group by tradeDate";

			System.out.println(sqlx);

			rs = st.executeQuery(sqlx);
			// rs = st.executeQuery(sqltotal);

			// rsmd = rs.getMetaData();

		} catch (Exception e) {

			e.printStackTrace();
			throw new ViewException("查询出错，请重启再试");
		}


		return rs;
	}

	//cardID: 若为0，则读取所有数据，否则读取对应卡号ID的数据
	public static ResultSet getViewRecord(String viewName, String cardID) throws ViewException{
		Context c = Context.newInstance();

		Statement st = null;

		ResultSet rs = null;


		try {

			Class.forName("com.mysql.jdbc.Driver");


			Connection con = DriverManager.getConnection(c.getConnection(),
					c.getUsrName(), c.getPsw());

			st = con.createStatement();

			String sqlx = "select tradeDate, sum from " + viewName;
			//单卡数据查询
			if(!cardID.equals("0")) {
				sqlx += " where cardID = '" + cardID + "'";
			}
			
			System.out.println(sqlx);

			rs = st.executeQuery(sqlx);

		} catch (Exception e) {

			e.printStackTrace();
			throw new ViewException("查询出错，请重启再试");
		}

		return rs;
	}
	
	
	//cardID: 若为0，则读取所有数据，否则读取对应卡号ID的数据
	//mark: "recharge,query,other,pay"
	public static ResultSet getNetworkPayRecord(String tableName, String mark, String cardID) throws ViewException{
		Context c = Context.newInstance();

		Statement st = null;

		ResultSet rs = null;


		try {

			Class.forName("com.mysql.jdbc.Driver");


			Connection con = DriverManager.getConnection(c.getConnection(),
					c.getUsrName(), c.getPsw());

			st = con.createStatement();

			String sqlx = "select tradeDate, sum(amount) as sum from " + tableName +
					" where tradeType='"+mark+"'";
			//单卡数据查询
			if(!cardID.equals("0")) {
				sqlx += " and ID = '" + cardID + "'";
			}
			sqlx += " group by tradeDate";
			
			System.out.println(sqlx);

			rs = st.executeQuery(sqlx);

		} catch (Exception e) {

			e.printStackTrace();
			throw new ViewException("查询出错，请重启再试");
		}

		return rs;
	}
	
	
	//根据开始时间，卡号查询结果
	public static ResultSet getNewPayRecord(String cardID, String date) throws ViewException{
		Context c = Context.newInstance();

		Statement st = null;

		ResultSet rs = null;

		// ResultSetMetaData rsmd = null;

		String tableName = "networkpay";

		// String quaryID = "'5088010100000011771'";


		try {

			Class.forName("com.mysql.jdbc.Driver");


			Connection con = DriverManager.getConnection(c.getConnection(),
					c.getUsrName(), c.getPsw());

			st = con.createStatement();

			String sqlx = "select * from "
					+ tableName + " where tradeDate >= '" + date + "' ";
			if(!cardID.equals(""))
				sqlx += "and ID = '" + cardID + "'";
			// String sqltotal =
			// "select tradeDate, sum(amount) from newpayrecord where tradeType = '01C00121' or tradeType = 'Z3004' group by tradeDate";

			// / String sqlall =
			// "select tradeDate,tradeTime, sum(amount) from newpayrecord group by tradeDate";

			System.out.println(sqlx);

			rs = st.executeQuery(sqlx);
			// rs = st.executeQuery(sqltotal);

			// rsmd = rs.getMetaData();

		} catch (Exception e) {

			e.printStackTrace();
			throw new ViewException("查询出错，请重启再试");
		}


		return rs;
	}
}
